package nl.ruudshuis.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBmanager {

	private static DBmanager uniqueInstance = null;

	private Connection con = null;
	private final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private final String dbname = "jdbc:derby:C:/Users/Niek/workspace/web/Ruuds VeilingHuisje/db;create=true";

	private DBmanager() {
		if (!dbExists(dbname)) {
			System.err.println("de database bestaat niet....");
		}
		Statement stmt;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * from VEILING");

			rs.close();
		} catch (SQLException e) {
			try {
				stmt = con.createStatement();
				stmt.execute("CREATE TABLE VEILING (id INTEGER, imgPath VARCHAR(100), titel VARCHAR(50), omschrijving TEXT, prijs DOUBLE, begintijd DATE, eindtijd DATE, PRIMARY KEY (id))");
				stmt.execute("CREATE TABLE GEBRUIKER (createdOn DATE, timesVisited INTEGER, fullname VARCHAR(50) NOT NULL, lastUsedOn DATE, PRIMARY KEY (id))");
				stmt.execute("CREATE TABLE BIEDING (veilingId INTEGER, userId INTEGER, prijs DOUBLE, PRIMARY KEY (veilingId, userId, prijs), FOREIGN KEY(veilingId) REFERENCES VEILING(id), FOREIGN KEY(userId) REFERENCES GEBRUIKER(id) )");
				stmt.close();
				con.close();

			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}

	public static synchronized DBmanager getInstance() {
		if (uniqueInstance == null) {
			uniqueInstance = new DBmanager();
		}
		return uniqueInstance;
	}

	private boolean dbExists(String dataSource) {
		boolean exists = false;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(dataSource);

			exists = true;
		} catch (Exception e) {
			; // Do nothing, as DB does not (yet) exist
		}
		return (exists);
	}

	public void close() {
		try {
			con = DriverManager.getConnection(dbname + ";shutdown=true");
		} catch (SQLException se) {
			; // Do Nothing. System has shut down.
			con = null;
		}
	}

	public Connection getConnection() {
		return con;
	}
}